<?php

class Contacts_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    /*
     * Returns all contact types, or contact types of a contact if specified
     */
	function get_contact_types($map_ids_to_value=false,$contact_id=NULL) {
		$contact_types = NULL;
		if ($contact_id===NULL) {
			$contact_types = $this->db->get('contact_types')->result();
		} else {
			$contact_types = $this->db->query("
				SELECT
					ct.*
				FROM
					contacts_to_types AS ctt
					LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
				WHERE
					ctt.contact_id = '".$contact_id."'
			")->result();
		}
		$result = array();
		if ($map_ids_to_value===true) {
			foreach ($contact_types as $obj) {
				$result[$obj->contact_type_id] = $obj->name;
			}
		} else {
			$result = $contact_types;
		}
		return $result;
	}
	
	/*
	 * Return all companies
	 */
	function get_companies($map_ids_to_value=false) {
		$companies = $this->db->get('contact_companies')->result();
		$result = array();
		if ($map_ids_to_value===true) {
			foreach ($companies as $obj) {
				$result[$obj->contact_id] = $obj->name;
			}
		}
		return $result;
	}
	
	/*
	 * $info['contact_id']
	 * $info['category'] => 'person' or 'company'
	 */
	function get_contact_details($info) {
		
		$result = array();
		$sql = '';
		
		//start a database transaction
		$this->db->trans_start();
		
		if ($info->category=='person') {
			
			//get contact general info
			$result['contact'] = $this->db->query("
				SELECT
					cp.contact_id AS contact_id,
					CONCAT(cp.first_name,' ',cp.last_name) AS name,
					cc.name AS company_name,
					cc.contact_id AS company_contact_id,
					c.is_employee,
					c.is_person,
					GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_types
				FROM
					contacts AS c
					JOIN contact_persons AS cp ON c.contact_id = cp.contact_id
					LEFT JOIN contacts_to_companies AS ctc ON ctc.contact_id = cp.contact_id
					LEFT JOIN contact_companies AS cc ON cc.contact_id = ctc.company_contact_id
					LEFT JOIN contacts_to_types AS ctt ON ctt.contact_id = cp.contact_id
					LEFT JOIN contact_types AS ct ON ct.contact_type_id = ctt.contact_type_id
				WHERE
					c.contact_id = '".$info->contact_id."'
				GROUP BY
					cp.contact_id
			")->row();
			
		}
		
		if ($info->category=='company') {
			
			//get contact general info
			$result['contact'] = $this->db->query("
				SELECT
					cc.contact_id AS contact_id,
					'' AS name,
					cc.name AS company_name,
					c.is_person,
					GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_types
				FROM
					contacts AS c
					JOIN contact_companies AS cc ON c.contact_id = cc.contact_id
					LEFT JOIN contacts_to_types AS ctt ON ctt.contact_id = cc.contact_id
					LEFT JOIN contact_types AS ct ON ct.contact_type_id = ctt.contact_type_id
				WHERE
					c.contact_id = '".$info->contact_id."'
				GROUP BY
					cc.contact_id
			")->row();
			
		}
		
		//get contact fields
		$result['contact_fields'] = $this->db->query("
			SELECT
				cf.name,
				ctf.value
			FROM
				contacts_to_fields AS ctf
				LEFT JOIN contact_fields AS cf ON ctf.contact_field_id = cf.contact_field_id
			WHERE
				ctf.contact_id = '".$info->contact_id."'
			ORDER BY
				cf.name
		")->result();

		//complete the transaction
		$this->db->trans_complete();
		$transaction_result = $this->db->trans_status();
		
		if ($transaction_result) {
			return $result;
		} else {
			return false;
		}
	
	}
	
    /*
     * Returns all fields allowed for the specified contact type
     */
	function get_fields_for_contact_type($contact_type) {
		$this->db->get("
			SELECT
				cf.*
			FROM
				contact_types_to_fields AS cttf
			JOIN
				contact_types AS ct ON cttf.contact_type_id=ct.contact_type_id
			JOIN
				contact_fields AS cf ON cttf.contact_field_id=cf.contact_field_id
			WHERE
				ct.contact_type_id = '".$contact_type."'
		")->result();
	}
	
	/* -------------------------------------------------------------------------------------------------------------
	 * Reports
	 */
	function create_report($search) {
		
		$non_contact_type_ids = array('persons','companies','employees','all_contact_types');
		$contact_type_ids = array_diff($search->options,$non_contact_type_ids);
		
		$sql = '';
		
		//filter by persons
		if (in_array('persons',$search->options)) {
			
			$sql .= "(
				SELECT
					cp.contact_id,
					CONCAT(cp.first_name,' ',cp.last_name) AS name,
					c.is_person,
					" . /* GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_type, */ "
					cc.name AS description
				FROM
					contacts AS c
					LEFT JOIN contact_persons AS cp ON c.contact_id = cp.contact_id
										
					LEFT JOIN contacts_to_companies AS ctc ON cp.contact_id = ctc.contact_id
					LEFT JOIN contact_companies AS cc ON ctc.company_contact_id = cc.contact_id
					
					LEFT JOIN contacts_to_types AS ctt ON cc.contact_id = ctt.contact_id
					LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
				WHERE (
					cp.first_name LIKE '%".($search->query)."%'
					OR cp.last_name LIKE '%".($search->query)."%'
				)
			";
			
			//filter by persons : contact type
			if (!in_array('all_contact_types',$search->options)) {
				$more_filter = in_array('employees',$search->options) || !empty($contact_type_ids);
				if ($more_filter) {
					$sql .= " AND ( ";
				}
					$count = 0;
					if (in_array('employees',$search->options)) {
						$sql .= " c.is_employee = 'y' ";
						$count++;
					}
					foreach($contact_type_ids as $contact_type_id) {
						$sql .= ($count>0) ? " OR " : "";
						$sql .= " ctt.contact_type_id = '".$contact_type_id."' ";
						$count++;
					}
				if ($more_filter) {
					$sql .= ")";
				}
			}
						
			//$sql .= "GROUP BY cp.contact_id )";
			$sql .= ")";
			
		}
		
		//filter by companies
		if (in_array('companies',$search->options)) {
			if ($sql!='') {
				$sql .= " UNION ";
			}
			$sql .= "(
				SELECT
					cc.contact_id,
					cc.name,
					c.is_person,
					" . /* GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_type, */ "
					'' AS description
				FROM
					contacts AS c
					LEFT JOIN contact_companies AS cc ON c.contact_id = cc.contact_id
					LEFT JOIN contacts_to_types AS ctt ON cc.contact_id = ctt.contact_id
					LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
				WHERE (
					 cc.name LIKE '%".($search->query)."%'
				)
			";
			
			//filter by companies : contact type
			if (!in_array('all_contact_types',$search->options)) {
				$more_filter = !empty($contact_type_ids);
				if ($more_filter) {
					$sql .= " AND ( ";
				}
					$count = 0;
					foreach($contact_type_ids as $contact_type_id) {
						$sql .= ($count>0) ? " OR " : "";
						$sql .= " ctt.contact_type_id = '".$contact_type_id."' ";
						$count++;
					}
				if ($more_filter) {
					$sql .= ")";
				}
			}
						
			//$sql .= " GROUP BY cc.contact_id )";			
			$sql .= ")";			
		}
		
		
		//sort the results
		if ($sql!='') {
			$sql .= " ORDER BY name, contact_id";
		}		
		
		//return the results
		return $this->db->query($sql)->result();
	}
	
	/* -------------------------------------------------------------------------------------------------------------
	 * Search
	 */
	function search($search) {
		
		$non_contact_type_ids = array('persons','companies','employees','all_contact_types');
		$contact_type_ids = array_diff($search->options,$non_contact_type_ids);
		
		$sql = '';
		
		//filter by persons
		if (in_array('persons',$search->options)) {
			
			$sql .= "(
				SELECT
					cp.contact_id,
					CONCAT(cp.first_name,' ',cp.last_name) AS name,
					c.is_person,
					" . /* GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_type, */ "
					cc.name AS description
				FROM
					contacts AS c
					LEFT JOIN contact_persons AS cp ON c.contact_id = cp.contact_id
										
					LEFT JOIN contacts_to_companies AS ctc ON cp.contact_id = ctc.contact_id
					LEFT JOIN contact_companies AS cc ON ctc.company_contact_id = cc.contact_id
					
					LEFT JOIN contacts_to_types AS ctt ON cc.contact_id = ctt.contact_id
					LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
				WHERE (
					cp.first_name LIKE '%".($search->query)."%'
					OR cp.last_name LIKE '%".($search->query)."%'
				)
			";
			
			//filter by persons : contact type
			if (!in_array('all_contact_types',$search->options)) {
				$more_filter = in_array('employees',$search->options) || !empty($contact_type_ids);
				if ($more_filter) {
					$sql .= " AND ( ";
				}
					$count = 0;
					if (in_array('employees',$search->options)) {
						$sql .= " c.is_employee = 'y' ";
						$count++;
					}
					foreach($contact_type_ids as $contact_type_id) {
						$sql .= ($count>0) ? " OR " : "";
						$sql .= " ctt.contact_type_id = '".$contact_type_id."' ";
						$count++;
					}
				if ($more_filter) {
					$sql .= ")";
				}
			}
						
			//$sql .= "GROUP BY cp.contact_id )";
			$sql .= ")";
			
		}
		
		//filter by companies
		if (in_array('companies',$search->options)) {
			if ($sql!='') {
				$sql .= " UNION ";
			}
			$sql .= "(
				SELECT
					cc.contact_id,
					cc.name,
					c.is_person,
					" . /* GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_type, */ "
					'' AS description
				FROM
					contacts AS c
					LEFT JOIN contact_companies AS cc ON c.contact_id = cc.contact_id
					LEFT JOIN contacts_to_types AS ctt ON cc.contact_id = ctt.contact_id
					LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
				WHERE (
					 cc.name LIKE '%".($search->query)."%'
				)
			";
			
			//filter by companies : contact type
			if (!in_array('all_contact_types',$search->options)) {
				$more_filter = !empty($contact_type_ids);
				if ($more_filter) {
					$sql .= " AND ( ";
				}
					$count = 0;
					foreach($contact_type_ids as $contact_type_id) {
						$sql .= ($count>0) ? " OR " : "";
						$sql .= " ctt.contact_type_id = '".$contact_type_id."' ";
						$count++;
					}
				if ($more_filter) {
					$sql .= ")";
				}
			}
						
			//$sql .= " GROUP BY cc.contact_id )";			
			$sql .= ")";			
		}
		
		
		//sort the results
		if ($sql!='') {
			$sql .= " ORDER BY name, contact_id";
		}		
		
		//return the results
		return $this->db->query($sql)->result();
	}
	
	/* -------------------------------------------------------------------------------------------------------------
	 * New Person
	 */	
	 
	// save new person
	function save_new_person($person) {
		//start a database transaction
		$this->db->trans_start();
		
		//create the contact
		$this->db->query("
			INSERT INTO contacts (
				contact_id,
				is_person, 
				is_employee, 
				is_active, 
				username, 
				password
			) VALUES (
				NULL,
				'y',
				'".( in_array('employee', $person->contact_type_ids) ? 'y' : 'n' )."',
				'y',
				NULL,
				NULL
			)
		");
		$contact_id = $this->db->insert_id();
		
		//create the contact in the persons table
		$this->db->query("
			INSERT INTO contact_persons (
				contact_id,
				first_name, 
				last_name
			) VALUES (
				'".$contact_id."',
				'".$person->first_name."',
				'".$person->last_name."'
			)
		");
		
		//add the contact as an employee of a company
		if ($person->company_id!='-1') {
			$this->db->query("
				INSERT INTO contacts_to_companies (
					contact_id,
					company_contact_id
				) VALUES (
					'".$contact_id."',
					'".$person->company_id."'
				)
			");
		}
		
		//classify the contact into types
		foreach($person->contact_type_ids as $contact_type_id) {
			if ('-1'==$contact_type_id || 'employee'==$contact_type_id) { continue; }
			$this->db->query("
				INSERT INTO contacts_to_types (
					contact_id,
					contact_type_id
				) VALUES (
					'".$contact_id."',
					'".$contact_type_id."'
				)
			");
		}
		
		//save additional fields
		foreach($person->contact_fields as $contact_field_id => $values) {
			
			foreach($values as $actual_value) {
				$this->db->query("
					INSERT INTO contacts_to_fields (
						contact_id,
						contact_field_id,
						value
					) VALUES (
						'".$contact_id."',
						'".$contact_field_id."',
						'".$actual_value."'
					)
				");
			}
		}
		
		//complete the transaction
		//i.e. if all queries above is successful, commit everything.
		//if any of the queries above failed, rollback everything
		$this->db->trans_complete();
		
		//return success or failure (true/false)
		return $this->db->trans_status();
	}
	
	// get contact fields for specified contact types (id) and company id -----------------------------
	function get_contact_fields($info) {
		
		$result = array();
		
		if (count($info->contact_type_ids)===0 && isset($info->contact_company_id) && $info->contact_company_id=='-1') {
			return new stdClass();
		}
		
		$sql = '';
		
		if (count($info->contact_type_ids)>0) {
		
			$sql .= "
				(SELECT
					cf.contact_field_id,
					cf.name
				FROM
					contact_types_to_fields AS cttf
					JOIN contact_fields AS cf ON cttf.contact_field_id = cf.contact_field_id
				WHERE
			";
			
			$count = 0;
			foreach($info->contact_type_ids as $contact_type_id) {
				if ($count>0) {
					$sql .= " OR ";
				}
				$sql .= " cttf.contact_type_id = '".$contact_type_id."'";
				$count++;
			}
			
			$sql .= " GROUP BY name)";
		}
		
		if (isset($info->contact_company_id) && $info->contact_company_id!='-1') {
			
			if (count($info->contact_type_ids)>0) {
				$sql .= " UNION ";
			}
			
			$sql .= " (
				SELECT
					cf.contact_field_id,
					cf.name
				FROM
					contact_types_to_fields AS cttf
					JOIN contact_fields AS cf ON cttf.contact_field_id = cf.contact_field_id
					JOIN contacts_to_types ctt ON ctt.contact_type_id = cttf.contact_type_id
				WHERE
					ctt.contact_id = '".$info->contact_company_id."'
				GROUP BY name
			)";
		}
		
		if ($sql != '') {
			$sql .= " ORDER BY name";
			$result = $this->db->query($sql)->result();
		}
		
		return $result;
		
	}
	
	
	
	/* -------------------------------------------------------------------------------------------------------------
	 * New Company
	 */	
	function save_new_company($company) {
		//start a database transaction
		$this->db->trans_start();
		
		//create the contact
		$this->db->query("
			INSERT INTO contacts (
				contact_id,
				is_person, 
				is_employee, 
				is_active, 
				username, 
				password
			) VALUES (
				NULL,
				'n',
				'n',
				'y',
				NULL,
				NULL
			)
		");
		$contact_id = $this->db->insert_id();
		
		//create the contact in the persons table
		$this->db->query("
			INSERT INTO contact_companies (
				contact_id,
				name
			) VALUES (
				'".$contact_id."',
				'".$company->name."'
			)
		");
		
		//classify the contact into types
		foreach($company->contact_type_ids as $contact_type_id) {
			$this->db->query("
				INSERT INTO contacts_to_types (
					contact_id,
					contact_type_id
				) VALUES (
					'".$contact_id."',
					'".$contact_type_id."'
				)
			");
		}
		
		//complete the transaction
		//i.e. if all queries above is successful, commit everything.
		//if any of the queries above failed, rollback everything
		$this->db->trans_complete();
		
		//return success or failure (true/false)
		return $this->db->trans_status();
	}
	
}
	
?>